﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZenHR.AttData.DAL.DB3Helper;

namespace ZenHR.AttData.DAL
{
    public  class BaseDAL
    {

        private string connectionString;
        public string ConnectionString
        { get => connectionString= @"Data Source="+ AppDomain.CurrentDomain.BaseDirectory + @"\DB_File\Jabil_ATT_DB.db;Pooling=true;FailIfMissing=false";
            set => connectionString = value;
        }


        private string taskdataconnectionString;
        /// <summary>
        /// 考勤数据存储分DB所在的库
        /// </summary>
        public string TaskDataConnectionString
        {
            get => taskdataconnectionString = @"Data Source=" + AppDomain.CurrentDomain.BaseDirectory + @"\DB_File\{0}.db;Pooling=true;FailIfMissing=false";
            set => taskdataconnectionString = value;
        }


        /// <summary>
        /// 判断DB3中表是否存在
        /// </summary>
        /// <param name="p_table_name"></param>
        /// <returns></returns>
        public Boolean TableIsExists(string p_table_name)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

           return _SqliteAccess.TableIsExists(p_table_name);
        }

        /// <summary>
        /// 获取任务执行配置信息（执行时间、链接信息、采集脚本）
        /// </summary>
        /// <returns></returns>
        public DataTable GetTaskRunTimeInfo(string task_id)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

            string _run_sql_str;

            if (task_id != "") 
            {
                _run_sql_str= string.Format("select id,task_code,run_time,last_run_time,sql_type,connectionString,sql_str,b.error_mail,current_index,mail_address,taskdb from task_run_time_info as a left join app_config as b where id='{0}'",task_id);
            }
            else
            {
                _run_sql_str = string.Format("select id,task_code,run_time,last_run_time,sql_type,connectionString,sql_str,b.error_mail,current_index,mail_address,taskdb from task_run_time_info as a left join app_config as b where IFNULL(datetime(last_run_time),'1990-01-01 00:00')<='{0}' and datetime('{1}')>( '{2}' || ' '|| run_time)", DateTime.Now.Date.ToString("yyyy-MM-dd 00:00:00"), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), DateTime.Now.ToString("yyyy-MM-dd"));
            }

            return _SqliteAccess.FetchData(_run_sql_str);
        }

        /// <summary>
        /// 获取配置信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetAppConfig()
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

            return _SqliteAccess.FetchData(string.Format("select mail_host, mail_user_name,mail_pwd, mail_name, error_mail, co_code, zenhr_if, zenhr_iftoken, interface_id, oss_token, key from app_config LIMIT 1"));
        }

        /// <summary>
        /// 更新工具配置
        /// </summary>
        /// <returns></returns>
        public int UpdateAppConfig(string mail_host, string mail_user_name,string mail_pwd, string mail_name, string error_mail, string co_code, string zenhr_if, string zenhr_iftoken, string interface_id, string oss_token, string key)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

            return _SqliteAccess.ExecSql(string.Format("UPDATE app_config SET mail_host = '{0}', mail_user_name = '{1}', mail_pwd= '{10}' ,mail_name = '{2}', error_mail = '{3}', co_code = '{4}', zenhr_if = '{5}', zenhr_iftoken = '{6}', interface_id ='{7}' , oss_token = '{8}', key = '{9}' ", mail_host, mail_user_name, mail_name, error_mail, co_code, zenhr_if, zenhr_iftoken, interface_id, oss_token, key, mail_pwd));
        }



        /// <summary>
        /// 获取完整的执行时间配置表
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllTaskRunTimeInfo(string  p_task_code)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

            if (p_task_code != "")
            {
                return _SqliteAccess.FetchData(string.Format("select id,task_code,run_time,last_run_time,sql_type,connectionString,sql_str,current_index,mail_address,taskdb from task_run_time_info where task_code='{0}' ", p_task_code));
            }
            else
            {
                return _SqliteAccess.FetchData(string.Format("select id,task_code,run_time,last_run_time,sql_type,connectionString,sql_str,current_index,mail_address,taskdb from task_run_time_info "));
            }
            

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelTaskRunTimeInfo(string id)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

            return _SqliteAccess.ExecSql(string.Format("delete from  task_run_time_info where id= {0}", id));
        }

        /// <summary>
        /// 获取所有配置过的PA
        /// </summary>
        /// <returns></returns>
        public DataTable GetALLTaskCodeList()
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;
            return _SqliteAccess.FetchData("select '' as TaskCode UNION all select DISTINCT(task_code) as TaskCode from task_run_time_info");
        }

        /// <summary>
        /// 提交任务执行配置（更新最新执行时间）
        /// </summary>
        /// <param name="TaskID"></param>
        /// <returns></returns>
        public int SubmitTaskRunTimeInfo(string TaskID)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

            return _SqliteAccess.ExecSql(string.Format("UPDATE task_run_time_info SET  last_run_time= '{0}' WHERE id = {1} ", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), TaskID));
        }

        #region  卡点数据数据源相关 -------------------------------------------------------------------------------------------
        /// <summary>e
        /// 保存链接配置信息
        /// </summary>
        /// <returns></returns>
        public int SaveTaskRunTimeInfo(string taskdb,string id, string task_code, string run_time, string sql_type, string connString, string sql_str, string last_run_time, int current_index, string mail_address)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

            //业务数据所在DB
            SqliteAccess _TaskSqliteAccess = new SqliteAccess();
            _TaskSqliteAccess.ConnectionString = string.Format(TaskDataConnectionString,taskdb);

            sql_str = sql_str.Replace("'", "''");

            //格式换一下时间格式
            DateTime t = Convert.ToDateTime(last_run_time);
            last_run_time = t.ToString("yyyy-MM-dd HH:mm");

            //判断表是否存在不存在则创建
            if (_TaskSqliteAccess.TableIsExists("att_data_" + task_code) == false)
            {
                _TaskSqliteAccess.ExecSql(string.Format("CREATE TABLE {0} (index_id integer, batch_id text,task_id integer,emp_code TEXT,card_number text,check_time datetime,inorout TEXT,extend_info TEXT,create_dt datetime);", "att_data_" + task_code));
            }


            if (_SqliteAccess.FetchData(string.Format("select * from task_run_time_info where id={0}", id)).Rows.Count > 0)
            {

                return _SqliteAccess.ExecSql(string.Format("UPDATE task_run_time_info SET task_code = '{1}', run_time = '{2}',  sql_type = '{3}', connectionString = '{4}', sql_str = '{5}',last_run_time='{6}' ,current_index='{7}' ,mail_address='{8}',taskdb='{9}' WHERE id = {0} ", id, task_code, run_time, sql_type, connString, sql_str, last_run_time, current_index, mail_address,taskdb));

            }
            else
            {
                return _SqliteAccess.ExecSql(string.Format("INSERT INTO task_run_time_info (id, task_code, run_time, sql_type, connectionString, sql_str,last_run_time,current_index,mail_address,taskdb) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}','{7}','{8}','{9}')", id, task_code, run_time, sql_type, connString, sql_str, last_run_time, current_index, mail_address,taskdb));
            }
        }

        /// <summary>
        /// 删除链接配置信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delatt_data(string taskdb,string p_task_code)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = string.Format(TaskDataConnectionString, taskdb); ;

            return _SqliteAccess.ExecSql(string.Format("delete from  att_data_" + p_task_code));
        }

        /// <summary>
        /// 获取对应TaskID,TaskCode对应的考勤结果数据(自动检查表内上一批次，上一批次出现过的不返回)
        /// </summary>
        /// <param name="p_ConnectionString"></param>
        /// <returns></returns>
        public DataTable GetTaskAttData(string taskdb,string p_batch_id, string p_TaskID, string p_TaskCode)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = string.Format(TaskDataConnectionString,taskdb);

            //_SqliteAccess.ConnectionString = @"Data Source=F:\Jabil_ATT_DB0630\Jabil_ATT_DB.db;Pooling=true;FailIfMissing=false";
            //p_batch_id = "202106290900";
            //p_TaskID = "5";
            var _Sql_str = "";
            try
            {

                if (int.Parse(p_TaskID) > 900)
                {
                    _Sql_str = string.Format("select a.index_id,a.batch_id,a.task_id,a.emp_code,a.card_number,a.check_time,a.inorout,CASE WHEN a.extend_info = 'NoMapping' THEN '' ELSE a.extend_info END AS extend_info,a.create_dt,0 AS is_uploaded FROM	att_data_" + p_TaskCode + " AS a WHERE	a.batch_id = '{1}' 	AND a.task_id ={0} 	AND charindex ('20', a.check_time ) =1 AND a.check_time>= date('now', 'localtime', '-{2} day')", p_TaskID, p_batch_id, p_TaskID.Substring(p_TaskID.Length - 1, 1));

                }
                else
                {
                    _Sql_str = string.Format("select a.index_id,a.batch_id,a.task_id,a.emp_code,a.card_number,a.check_time,a.inorout,case when a.extend_info='NoMapping' then '' else a.extend_info end as extend_info,a.create_dt,case when ifnull(b.batch_id,'') <>'' then 1 else 0 end as is_uploaded from att_data_" + p_TaskCode + "  as a left join att_data_" + p_TaskCode + " as b on a.batch_id = '{1}' and b.batch_id = (select batch_id from att_data_" + p_TaskCode + " where batch_id <> '{1}' and task_id ={0} and extend_info<>'NoMapping' order by create_dt desc LIMIT 1) and a.emp_code = b.emp_code and a.card_number = b.card_number and a.check_time = b.check_time and a.extend_info = b.extend_info where a.batch_id = '{1}' and is_uploaded = 0 and a.task_id ={0} and charindex('20',a.check_time )=1", p_TaskID, p_batch_id);

                }
            }
            catch (Exception)
            {

                _Sql_str = string.Format("select a.index_id,a.batch_id,a.task_id,a.emp_code,a.card_number,a.check_time,a.inorout,case when a.extend_info='NoMapping' then '' else a.extend_info end as extend_info,a.create_dt,case when ifnull(b.batch_id,'') <>'' then 1 else 0 end as is_uploaded from att_data_" + p_TaskCode + "  as a left join att_data_" + p_TaskCode + " as b on a.batch_id = '{1}' and b.batch_id = (select batch_id from att_data_" + p_TaskCode + " where batch_id <> '{1}' and task_id ={0} and extend_info<>'NoMapping' order by create_dt desc LIMIT 1) and a.emp_code = b.emp_code and a.card_number = b.card_number and a.check_time = b.check_time and a.extend_info = b.extend_info where a.batch_id = '{1}' and is_uploaded = 0 and a.task_id ={0} and charindex('20',a.check_time )=1", p_TaskID, p_batch_id);

            }


            return _SqliteAccess.FetchData(_Sql_str);
        }

        /// <summary>
        /// 删除历史考勤数据（By TaskCode）
        /// </summary>
        /// <param name="p_ConnectionString"></param>
        /// <param name="p_TaskCode"></param>
        /// <param name="p_BatchID"></param>
        /// <returns></returns>
        public int DelHisAttData(string taskdb, string TaskID, string p_TaskCode, string p_BatchID)
        {

            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = string.Format(TaskDataConnectionString, taskdb); 

            return _SqliteAccess.ExecSql(string.Format("delete from att_data_" + p_TaskCode + " where   batch_id <> '{0}' and create_dt< date('now', '-7 day')  ", p_BatchID));

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_batch_id">批次ID</param>
        /// <param name="p_TaskID">任务ID</param>
        /// <param name="p_TaskInfoCode">采集事务编码</param>
        /// <param name="p_InsertData">需要插入数据库的数据</param>
        /// <param name="p_current_index"></param>
        /// <returns></returns>
        public string BatchInsertTaskAttData(string taskdb, string p_batch_id, string p_TaskID, string p_TaskInfoCode, DataTable p_InsertData)
        {
            //批量插入DB3执行语句
            string insert_str = "insert into att_data_" + p_TaskInfoCode + " (index_id,batch_id,task_id,emp_code,card_number, check_time,inorout,extend_info,create_dt) values(@index_id," + p_batch_id + "," + p_TaskID + ",@emp_code,@card_number, @check_time,@inorout,@extend_info,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";

            //初始Sql插入参数对象用的码表
            List<string> _para = new List<string>();
            _para.Add("emp_code");
            _para.Add("card_number");
            _para.Add("check_time");
            _para.Add("inorout");
            _para.Add("extend_info");
            _para.Add("index_id");

            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = string.Format(TaskDataConnectionString, taskdb); 

            return _SqliteAccess.BatchInsertData(insert_str, _para, p_InsertData);
        }

        /// <summary>
        /// 获取当前批次的数据
        /// </summary>
        /// <param name="p_tablename"></param>
        /// <returns></returns>
        public DataTable SearchCurrentBatchTaskData(string taskdb,string p_tablename, string batch_id)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = string.Format(TaskDataConnectionString, taskdb);

            return _SqliteAccess.FetchData(string.Format("select * from {0} where  batch_id='{1}'", p_tablename, batch_id));
        }

        /// <summary>
        /// 获取对应采集批次最新的一次BatchID
        /// </summary>
        /// <param name="task_id"></param>
        /// <returns></returns>
        public DataTable GetLastBatchID(string taskdb,string task_code)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = string.Format(TaskDataConnectionString, taskdb); 

            return _SqliteAccess.FetchData(string.Format("select DISTINCT(batch_id) as batch_id from {0} order by create_dt desc", "att_data_" + task_code));
        }

        /// <summary>
        /// 查询所有子数据集中的记录
        /// </summary>
        /// <param name="emp_code"></param>
        /// <returns></returns>
        public DataSet SearchAttDataFromAllData(string emp_code,string start_dt,string end_dt)
        {
            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

            var  _run_list=  _SqliteAccess.FetchData("select task_code,taskdb from task_run_time_info GROUP BY task_code,taskdb");

            DataSet ds = new DataSet();

            foreach (DataRow item in _run_list.Rows)
            {
                string _task_code = item[0].ToString();
                string _taskdb= item[1].ToString();

                //业务数据所在DB
                SqliteAccess _TaskSqliteAccess = new SqliteAccess();
                _TaskSqliteAccess.ConnectionString = string.Format(TaskDataConnectionString, _taskdb);

                var _subtable = _TaskSqliteAccess.FetchData(string.Format("select '{1}' as PA,emp_code,card_number,check_time,create_dt from att_data_{1} where (emp_code ='{0}' or card_number='{0}') and check_time BETWEEN '{2}' and '{3}'  ", emp_code, _task_code, start_dt, end_dt));

                if (_subtable != null)
                {

                    ds.Tables.Add(_subtable);
                }


            }

            return ds;
        }

        /// <summary>
        /// 更新对应采集任务最新
        /// </summary>
        /// <param name="task_code"></param>
        /// <returns></returns>
        public int UpdateCurrentIndex(string taskdb, string task_code)
        {
            SqliteAccess _TaskDataSqliteAccess = new SqliteAccess();
            _TaskDataSqliteAccess.ConnectionString = string.Format(TaskDataConnectionString, taskdb);

            SqliteAccess _SqliteAccess = new SqliteAccess();
            _SqliteAccess.ConnectionString = ConnectionString;

            int max_id = 0;

            //现已经获取的最大ID
            var _datatable= _TaskDataSqliteAccess.FetchData(string.Format("select MAX(index_id) from att_data_{0}", task_code));

            try
            {
                if (_datatable.Rows.Count > 0)
                {
                    int.TryParse(_datatable.Rows[0][0].ToString(), out max_id);
                }
            }
            catch (Exception)
            {
                
            }

            //将最大ID更新到配置库
            if (max_id > 0)
            {
                _SqliteAccess.ExecSql(string.Format("update task_run_time_info set current_index={1} where task_code='{0}' and current_index>=0", task_code, max_id));
                return 1;
            }
            else {
                return 0;
            }

        }

        #endregion  卡点数据数据源相关 -------------------------------------------------------------------------------------------



    }
}
